﻿using System.Threading.Tasks;
using HtmlAgilityPack;
using Magicodes.ExporterAndImporter.Core;
using Magicodes.ExporterAndImporter.Excel;
using SqlSugar;

namespace ConsoleApp
{
    public class Database
    {
        public static string ConnectionString = "Data Source=47.98.209.58;Initial Catalog=oas_wstx_com_cn;Persist Security Info=True;User ID=oas_wstx_com_cn;Password=3jRJdKEJQj";
        public static SqlSugarClient CretClient()
        {
            var db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConnectionString, //数据库连接字符串
                DbType = DbType.SqlServer, //必填
                IsAutoCloseConnection = false, //默认false
                InitKeyType = InitKeyType.Attribute
            });
            return db;
        }
    }

    public class Excel
    {
        public const string SqlString = "SELECT t1.Id, t1.Code, t1.CustomerName, t1.Contractor, t1.ContractorMobile, t1.MemberName, t2.PrtName, t1.SignDate, t2.StartDate, t2.EndDate, t2.RemindText, t2.ProMoney FROM Contracts t1 LEFT JOIN CotToPrt t2 ON t1.id = t2.CotId WHERE t2.PrtName = '营销型平台（V云3.0）' AND t2.Status = 1 AND t1.Status = 1 AND t2.StartDate >= '2020-01-01 00:00:00'";

        public async Task Export()
        {
            var db = Database.CretClient();
            var list = db.Ado.SqlQuery<Contracts>(GetSql());

            foreach (var item in list)
            {
                HtmlDocument doc = new HtmlDocument();
                var exSql = GetSql(item.Id);
                var formdata = db.Ado.SqlQuerySingle<FormDataModel>(exSql);
                if (formdata != null)
                {
                    doc.LoadHtml(formdata.FormData);
                    HtmlNode node = doc.GetElementbyId("textfield100");
                    string titleValue = node.Attributes["value"].Value;
                    item.OrderMoney = titleValue;
                }
                else
                {
                    item.OrderMoney = "0";
                }
            }

            IExporter exporter = new ExcelExporter();
            var exporterFilePath = "D://h.xlsx";
            await exporter.Export(exporterFilePath, list);
        }

        public string GetSql(string id = "")
        {
            if (string.IsNullOrEmpty(id))
            {
                return SqlString;
            }

            return $"SELECT FormData FROM BindFormToFormData t1, FormsBindToContract t2 where t2.ContractId = '{id}' and t1.BindFormId = t2.FormsBindId";
        }
    }
}
